/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bl;

import dal.Request;
import entity.Employee;
import entity.Holiday;
import entity.HolidayDetail;
import entity.LeaveDetail;
import entity.LeaveForm;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author Kien Chinh
 */
public class EmployeeBusiness {
    Request request = new Request();
    public static final int ADMIN = 0;
    public static final int MANAGER = 1;
    
    public boolean checkLeaveDateInLeaveDetail(Date date){
        java.sql.Date day = new java.sql.Date(date.getTime());
        String query = "SELECT * FROM LeaveDetail WHERE Startday in ('"+day+"')";
        return request.executeCheckQuery(query);
    }
    
    public boolean checkLeaveDateInHoliday(Date date){
        java.sql.Date day = new java.sql.Date(date.getTime());
        String query = "SELECT * FROM HolidayDetail WHERE Startday in ('"+day+"')";
        return request.executeCheckQuery(query);
    }
    
    public boolean addFormToDB(Employee e, List<LeaveDetail> list){
        LeaveForm form = new LeaveForm();
        form.setEmployee(e);
        form.setListLeave(list);
        return request.addLeaveTransaction(form);
    }
    
    public ArrayList<LeaveForm> getForm(int status,String departmentID){
        ArrayList<LeaveForm> list = request.getLeaveFormTransaction(status,departmentID);
        return list;
    }
    
    public boolean approveLeave(Employee employee,LeaveForm form){
        if(employee.getOrganization().getOrganizationLevelID()==ADMIN){
            if(request.updateQuery("UPDATE LeaveDetail SET [Status] = 0 WHERE LeaveFormID = "+form.getLeaveFormID()+"")>0){
                return true;
            }
        }else{
            if(request.updateQuery("UPDATE LeaveDetail SET [Status] = 1 WHERE LeaveFormID = "+form.getLeaveFormID()+"")>0){
                return true;
            }
        }
        return false;
    }
    
    public boolean cancelLeaveByForm(LeaveForm form,String cause){
        if(request.updateQuery("UPDATE LeaveDetail SET [Status] = 3 WHERE LeaveFormID = "+form.getLeaveFormID()+"")>0){
            request.updateQuery("UPDATE LeaveDetail SET Cause = '"+cause+"' WHERE LeaveFormID = "+form.getLeaveFormID()+"");
            return true;
        }
        return false;
    }
    
    public ArrayList<LeaveDetail> getLeaveDetailToCancel(Employee employee){
        String query = "SELECT LeaveDetail.LeaveDetailID,LeaveDetail.Startday,LeaveDetail.Finishday,LeaveDetail.[Status],LeaveDetail.Reson,LeaveDetail.TypeID,TypeName,Cause FROM LeaveDetail\n" +
                       "JOIN LeaveType\n" +
                       "ON LeaveDetail.TypeID = LeaveType.TypeID\n" +
                       "JOIN LeaveForm\n" +
                       "ON LeaveForm.LeaveFormID = LeaveDetail.LeaveFormID\n" +
                       "JOIN Employee\n" +
                       "ON LeaveForm.EmployeeID = Employee.EmployeeID\n" +
                       "WHERE Finishday > GETDATE() AND Employee.EmployeeID = ?\n" + 
                       "ORDER BY LeaveDetail.Finishday DESC";
        return request.getLeaveDetail(employee, query);
    }
    
    public ArrayList<LeaveDetail> getLeaveDetailToHistory(Employee employee){
        String query ="SELECT * FROM LeaveDetail\n" +
                        "JOIN LeaveForm\n" +
                        "ON LeaveDetail.LeaveFormID = LeaveForm.LeaveFormID\n" +
                        "JOIN LeaveType\n" +
                        "ON LeaveDetail.TypeID = LeaveType.TypeID\n" +
                        "WHERE LeaveForm.EmployeeID = ?\n" +
                        "ORDER BY LeaveDetail.Finishday DESC";
        return request.getLeaveDetail(employee, query);
    }
    
    public boolean cancelLeave(LeaveDetail detail){
        if(request.updateQuery("UPDATE LeaveDetail SET [Status] = 3 WHERE LeaveDetailID = "+detail.getLeaveDetailID()+"")>0){
            return true;
        }
        return false;
    }
    
    public int countLeaveDetail(Employee e,String year){
        String query = "SELECT COUNT(*) FROM LeaveDetail\n" +
                        "JOIN LeaveForm\n" +
                        "ON LeaveDetail.LeaveFormID = LeaveForm.LeaveFormID\n" +
                        "WHERE YEAR(Startday) = '"+year+"' AND LeaveForm.EmployeeID = "+e.getEmployeeID()+" AND LeaveDetail.[Status] = 0";
        return request.countLeave(query);
    }
    
    public int countLeaveDetailByMonth(Employee e, String month, int type, String year){
        String query = "SELECT COUNT(*) FROM LeaveDetail\n" +
                       "JOIN LeaveForm\n" +
                       "ON LeaveDetail.LeaveFormID = LeaveForm.LeaveFormID\n" +
                       "WHERE [Status] = 0 AND EmployeeID = "+e.getEmployeeID()+" AND MONTH(Startday) = '"+month+"' AND TypeID = "+type+" AND YEAR(Startday) = '"+year+"'";
        return request.countLeave(query);
    }
    
    public boolean addEmployee(Employee e){
        return request.addEmployee(e);
    }
    public boolean addHoliday(Holiday holiday){
        return request.addHoliday(holiday);
    }
}
